<?php
  include_once('./queries/utils.inc.php');


  function jobQueue_agg_query($groupFields, 
                              $aggFuncs,
                              $statusFilters, 
                              $connection) {
    $fldCount = 0;
    $sql = "SELECT ";
    foreach($groupFields as $group) {
      if ($fldCount>0) $sql = $sql . ", ";
      $sql = $sql . $group;
      $fldCount++;
    }
    
    foreach($aggFuncs as $agg) {
      if($fldCount>0) $sql = $sql . ", ";
      $sql = $sql . $agg["func"] . "(" . $agg["field"] . ")";
      $fldCount++;
    }
    
    $sql = $sql = $sql . " FROM clusterads_horizontal c, procads_horizontal p " .
                  "WHERE " . 
                  "c.cluster_id = p.cluster_id " .
                  "AND c.owner = '{$_SESSION["loginUsername"]}' " .
                  "AND c.scheddname = p.scheddname ";
    //get the jobstatus filters here
    if (count($statusFilters) > 0) {
      $sql = $sql . "AND (";
      $curFilter = 0;
      foreach($statusFilters as $status) {
        if ($curFilter > 0) $sql = $sql . " OR ";
        if ($status == "waiting") {
          $sql = $sql . "(p.jobstatus IS NULL OR p.jobstatus=0 OR p.jobstatus=1)";
        } elseif ($status == "held") {
          $sql = $sql . "(p.jobstatus=5)";
        } elseif ($status == "running") {
          $sql = $sql . "(p.jobstatus=2)";
        } elseif ($status == "completed") {
          $sql = $sql . "(p.jobstatus=4)";
        }
      }
      $sql = $sql . ") ";
    }

    //now the group by stuff
    if (count($groupFields)>0) {
      $sql = $sql . " GROUP BY ";
      $fldCount = 0;
      foreach($groupFields as $group) {
        if ($fldCount>0) $sql = $sql . ", ";
        $sql = $sql . $group;
        $fldCount++;
      }
    }
	//ORACLE seems to barf on semi colons at the end (!?!) so drop it
	//if it causes a postgreSQL error we'll special case it
    //$sql = $sql . ";";
    //echo $sql;
    $result = get_recordset($connection, $sql);
    return $result;
  }

  function count_user_jobs_by_status($connection, $statusFilters) {
    $groupBy = array();
    $aggs = array();
    $aggs[0] = array();
    $aggs[0]["func"] = "count";
    $aggs[0]["field"] = "*";
  
    return get_scalar(jobQueue_agg_query($groupBy, $aggs, 
                      $statusFilters, $connection));
  }





  
  function jobQueueList($connection, $index, $params, $statusFilters, 
                        $owner, $pn) {
   
    $effOwner = $owner;
    if ($effOwner == "<b>Total</b>") unset($owner);
    if ($owner === NULL) unset($owner);
    $sql = "SELECT p.scheddname as schedd, p.cluster_id as clusterid, " .
           "p.proc_id as procid, c.owner, p.jobstatus, " .
           "p.imagesize, p.remotewallclocktime, p.remotehost " .
           "FROM clusterads_horizontal c, procads_horizontal p " .
       	   "WHERE c.cluster_id = p.cluster_id ";
           if ($_SESSION["administrator"] === true) {
             if (isset($owner)) $sql = $sql . "AND c.owner = '{$owner}' ";
           } else {
             //not an admin, so force jobQueue privacy
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
           $sql = $sql . "AND c.scheddname = p.scheddname ";

    if(isset($params)) {
      foreach($params as $field => $value) {
        $sql = $sql . " AND {$field}='{$value}'";
      }
    }
    
    //get the jobstatus filters here
    if (count($statusFilters) > 0) {
      $sql = $sql . "AND (";
      $curFilter = 0;
      foreach($statusFilters as $status) {
        if ($curFilter > 0) $sql = $sql . " OR ";
        if ($status == "waiting") {
          $sql = $sql . "(p.jobstatus IS NULL OR p.jobstatus=0 OR p.jobstatus=1)";
        } elseif ($status == "held") {
          $sql = $sql . "(p.jobstatus=5)";
        } elseif ($status == "running") {
          $sql = $sql . "(p.jobstatus=2)";
        } elseif ($status == "completed") {
          $sql = $sql . "(p.jobstatus=4)";
        }
      }
      $sql = $sql . ") ";
    }

    $rcount = get_recordcount($connection, $sql);
    //echo $sql;
    $sql = $sql . "ORDER BY schedd, clusterid, procid ";
    $sql = do_pagination_rewriting($pn, $rcount, $sql);

    //echo $sql;
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["params"] = $params;
    $_SESSION["{$index}"]["status"] = $statusFilters;
    $_SESSION["{$index}"]["owner"] = $effOwner;
    //$tmp = isset($owner);
    //echo "tmp:" . $tmp;

    $_SESSION["{$index}"]["title"]="Paramaterized job queue list.";        
    $_SESSION["{$index}"]["message"] = "Paramaterized job queue list.";
    
    $fields = array();
    $fields[0] = "schedd";
    $fields[1] = "clusterid";
    $fields[2] = "procid";
    $fields[3] = "owner";
    $fields[4] = "jobstatus";
    $fields[5] = "imagesize";
    $fields[6] = "remotewallclocktime";
    $fields[7] = "remote host";
    //var_dump($_SESSION["{$index}"]["fields"]);

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));

    return $rs;
    
    //echo $sql;
  }

  function single_proc_short_query($connection, $index, $schedd, $cid, $pid) { 
    $date = TS_FORMAT;
    $sql = "SELECT p.scheddname, p.cluster_id, p.proc_id, c.owner, " .
           "to_char(c.qdate, '{$date}'), p.jobstatus, p.imagesize, c.cmd " .
           "FROM clusterads_horizontal c, procads_horizontal p " .
           "WHERE c.scheddname=p.scheddname AND c.cluster_id=p.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
            $sql = $sql . "AND p.scheddname='{$schedd}' " .
            "AND p.cluster_id='{$cid}' " .
            "AND p.proc_id='{$pid}' "; // oracle semicolon issues. ";";
    //echo $sql;
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();

    $_SESSION["{$index}"]["title"]="Latest information about job {$schedd}.{$cid}.{$pid}";
    $_SESSION["{$index}"]["message"]="Latest information about job {$schedd}.{$cid}.{$pid}";

    $fields = array();
    $fields[0] = "scheddname";
    $fields[1] = "cid";
    $fields[2] = "pid";
    $fields[3] = "owner";
    $fields[4] = "qdate";
    $fields[5] = "jobstatus";
    $fields[6] = "imagesize";
    $fields[7] = "cmd";

    $rs = new recordset(1, $fields, get_recordset($connection, $sql));
    return $rs;
  }

  function single_proc_horizontal_query($connection, $index, $schedd, 
                                        $cid, $pid) {
    $date = TS_FORMAT;
    $sql = "SELECT p.scheddname, p.cluster_id, p.proc_id, c.owner, " .
           "to_char(c.qdate, '{$date}'), c.cmd, c.args, " .
           "p.jobstatus, p.imagesize, p.remotewallclocktime, p.globaljobid " .
           "FROM clusterads_horizontal c, procads_horizontal p " .
           "WHERE c.scheddname=p.scheddname AND c.cluster_id=p.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
            $sql = $sql . "AND p.scheddname='{$schedd}' " .
            "AND p.cluster_id='{$cid}' " .
            "AND p.proc_id='{$pid}' "; //oracle semicolon issues . ";";
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();

    $_SESSION["{$index}"]["title"]="Latest information about job {$schedd}.{$cid}.{$pid}";
    $_SESSION["{$index}"]["message"]="Latest information about job {$schedd}.{$cid}.{$pid}";

    $fields = array();
    $fields[0] = "scheddname";
    $fields[1] = "cid";
    $fields[2] = "pid";
    $fields[3] = "owner";
    $fields[4] = "qdate";
    $fields[5] = "cmd";
    $fields[6] = "args";
    $fields[7] = "jobstatus";
    $fields[8] = "imagesize";
    $fields[9] = "remotewallclocktime";
    $fields[10] = "globaljobid";

    $rs = new recordset(1, $fields, get_recordset($connection, $sql));
    return $rs;
  }

  function single_cluster_vertical_query($connection, $index, $schedd, $cid) {
    $sql = "SELECT cv.attr, cv.val " .
           "FROM clusterads_vertical cv, clusterads_horizontal c " .
           "WHERE c.scheddname=cv.scheddname AND c.cluster_id=cv.cluster_id " .
            "AND cv.scheddname='{$schedd}' AND cv.cluster_id='{$cid}' ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
           $rcount = get_recordcount($connection, $sql);
           //$sql = $sql . ";";

    //echo $sql;
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();

    $_SESSION["{$index}"]["title"]="Latest information about job {$schedd}.{$cid}";
    $_SESSION["{$index}"]["message"]="Latest information about job {$schedd}.{$cid}";

    $fields = array();
    $fields[0] = "Attribute";
    $fields[1] = "Value";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }

  function single_proc_vertical_query($connection, $index, $schedd, 
                                      $cid, $pid) {
    $sql = "SELECT pv.attr, pv.val " .
           "FROM procads_vertical pv, clusterads_horizontal c " .
           "WHERE c.scheddname=pv.scheddname AND c.cluster_id=pv.cluster_id " .
           "AND pv.scheddname='{$schedd}' " .
           "AND pv.cluster_id='{$cid}' " .
           "AND pv.proc_id='{$pid}' ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
		   $rcount = get_recordcount($connection, $sql);
           //$sql = $sql . ";";
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();

    $_SESSION["{$index}"]["title"]="Latest information about job {$schedd}.{$cid}.{$pid}";
    $_SESSION["{$index}"]["message"]="Latest information about job {$schedd}.{$cid}.{$pid}";

    $fields = array();
    $fields[0] = "Attribute";
    $fields[1] = "Value";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }

  function single_proc_rejects_query($connection, $index, $schedd, 
                                     $cid, $pid) {

    $date = TS_FORMAT;     
    $sql = "SELECT to_char(r.reject_time, '{$date}') AS reject_time, " .
           "r.username, r.startdname, r.diagnosis " .
           "FROM rejects r, clusterads_horizontal c " .
           "WHERE r.scheddname='{$schedd}' " .
              "AND r.cluster_id={$cid} ".
              "AND r.proc_id={$pid} " .
              "AND r.scheddname=c.scheddname " .
              "AND r.cluster_id=c.cluster_id " . 
	      "AND r.reject_time >= current_timestamp - " . get_interval(1, 'hour');
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . " AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
           $sql = $sql . " ORDER BY reject_time desc "; //oracle semicolon issues . ";";

    //echo $sql;
    $result=get_recordset($connection, $sql);
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Current information about rejects for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Current information about rejects for job {$schedd}.{$cid}.{$pid}.";

    $_SESSION["{$index}"]["recSet"] = get_recordset($connection, $sql);
    $_SESSION["{$index}"]["time"] = date("r", time());
    $_SESSION["{$index}"]["fields"]=array();
    $_SESSION["{$index}"]["fields"][0]="reject_time";
    $_SESSION["{$index}"]["fields"][1]="username";
    //$_SESSION["{$index}"]["fields"][2]="scheddname";
    //$_SESSION["{$index}"]["fields"][3]="cluster";
    //$_SESSION["{$index}"]["fields"][4]="proc";
    //$_SESSION["{$index}"]["fields"][5]="globaljobid";
    $_SESSION["{$index}"]["fields"][2]="startdname";
    $_SESSION["{$index}"]["fields"][3]="diagnosis";

  }

  function single_proc_matches_query($connection, $index, $schedd, 
                                     $cid, $pid) {

    $date = TS_FORMAT;
    $sql = "SELECT to_char(m.match_time, '{$date}') as match_time, " .
           "m.username, m.machine_id, m.remote_user, m.remote_priority " .
           "FROM matches m, clusterads_horizontal c " .
           "WHERE m.scheddname='{$schedd}' " .
              "AND m.cluster_id='{$cid}' ".
              "AND m.proc_id='{$pid}' " .
              "AND m.scheddname=c.scheddname " .
              "AND m.cluster_id=c.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
           $sql = $sql . "ORDER BY match_time desc "; //oracle ; issues . ";";

    //echo $sql;
    $result=get_recordset($connection, $sql);
    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Historical information about matches for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Historical information about matches for job {$schedd}.{$cid}.{$pid}.";

    $_SESSION["{$index}"]["recSet"] = get_recordset($connection, $sql);
    $_SESSION["{$index}"]["time"] = date("r", time());

    $_SESSION["{$index}"]["fields"]=array();
    $_SESSION["{$index}"]["fields"][0]="match_time";
    $_SESSION["{$index}"]["fields"][1]="username";
    //$_SESSION["{$index}"]["fields"][2]="scheddname";
    //$_SESSION["{$index}"]["fields"][3]="cluster";
    //$_SESSION["{$index}"]["fields"][4]="proc";
    //$_SESSION["{$index}"]["fields"][5]="globaljobid";
    $_SESSION["{$index}"]["fields"][2]="startdname";
    $_SESSION["{$index}"]["fields"][3]="remote_user";
    $_SESSION["{$index}"]["fields"][4]="remote_priority";

  }

  function single_proc_runs_query($connection, $index, $schedd, $cid, $pid) {

    $date = TS_FORMAT;
    $sql = "SELECT r.run_id, r.machine_id, r.spid, " .
           "to_char(r.startts, '{$date}'), " .
           "to_char(r.endts, '{$date}') as endts, " .
           "r.endtype, r.endmessage " .
           "FROM runs r, clusterads_horizontal c " .
           "WHERE r.scheddname='{$schedd}' " .
              "AND r.cluster_id='{$cid}' ".
              "AND r.proc_id='{$pid}' " .
              "AND r.scheddname=c.scheddname " .
              "AND r.cluster_id=c.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
		   $rcount = get_recordcount($connection, $sql);
           $sql = $sql . "ORDER BY endts desc "; //oracle ; issues . ";";

    //echo $sql;

    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Current information about runs for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Current information about runs for job {$schedd}.{$cid}.{$pid}.";

    $fields=array();
    $fields[0]="run_id";
    $fields[1]="machine_id";
    $fields[2]="spid";
    $fields[3]="startts";
    $fields[4]="endts";
    $fields[5]="endtype";
    $fields[6]="endmessage";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }

	// events related
  function single_proc_events_query($connection, $index, $schedd, $cid, $pid) {

    $date = TS_FORMAT;
    $sql = "SELECT e.eventtype, " .
           "to_char(e.eventtime, '{$date}') AS eventtime, e.description " .
           "FROM events e, clusterads_horizontal c " .
           "WHERE e.scheddname='{$schedd}' " .
              "AND e.cluster_id='{$cid}' ".
              "AND e.proc_id='{$pid}' " .
              "AND e.scheddname=c.scheddname " .
              "AND e.cluster_id=c.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
		   $rcount = get_recordcount($connection, $sql);
           $sql = $sql . "ORDER BY eventtime desc "; //oracle ; issues . ";";

    //echo $sql;

    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Current information about events for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Current information about events for job {$schedd}.{$cid}.{$pid}.";

    $fields=array();
    $fields[0]="eventtype";
    $fields[1]="eventtime";
    $fields[2]="description";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }

  function single_proc_rejects_summary($connection, $index, $schedd, 
                                       $cid, $pid) {

    //$tempts = date('d-M-Y h.i.s A P', time()-3600);

    // PHP time zone is 4 digits (e.g., -0500) but database time zone
    // is two digits (e.g., -05). So need to add the time zone 
    // in the query string.
    // time() is giving GMT, so add +00 to query string.
    $tempts = date('Y-m-d h:i:s', time()-3600);

    $date = TS_FORMAT;
    $sql = "SELECT to_char(r.reject_time, '{$date}') AS reject_time, " .
           "r.username, r.scheddname, r.cluster_id, r.proc_id " . 
           "FROM rejects r, clusterads_horizontal c " .
           "WHERE r.scheddname='{$schedd}' " .
              "AND r.cluster_id={$cid} ".
              "AND r.proc_id={$pid} " .
              "AND r.scheddname=c.scheddname " .
              "AND r.cluster_id=c.cluster_id " .
	      "AND reject_time >= '{$tempts}+00' ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
           // $sql = $sql . "GROUP BY reject_time, r.scheddname, r.cluster_id, r.proc_id ";
           $rcount = get_recordcount($connection, $sql);
           $sql = $sql . 
	   "ORDER BY r.scheddname, r.cluster_id, r.proc_id, reject_time desc";

    //echo $sql;

    unset($_SESSION["{$index}"]);
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Current information about rejects for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Current information about rejects for job {$schedd}.{$cid}.{$pid}.";

    $fields=array();
    $fields[0]="reject_time";
    $fields[1]="user_name";
    $fields[2]="scheddname";
    $fields[3]="cluster";
    $fields[4]="proc";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }


  function single_proc_matches_summary($connection, $index, $schedd, 
                                       $cid, $pid) {
    $date = TS_FORMAT;
    $sql = "SELECT to_char(m.match_time, '{$date}') as match_time, " .
           "m.scheddname, m.cluster_id, m.proc_id, m.machine_id " . 
           "FROM matches m, clusterads_horizontal c " . 
           "WHERE m.scheddname='{$schedd}' " .
              "AND m.cluster_id='{$cid}' ". 
              //"AND (m.proc_id='{$pid}' or m.proc_id=0)" .
              "AND m.scheddname=c.scheddname " .
              "AND m.cluster_id=c.cluster_id ";
           if (!($_SESSION["administrator"] === true)) {
             $sql = $sql . "AND c.owner = '{$_SESSION["loginUsername"]}' ";
           }
		   $rcount = get_recordcount($connection, $sql);
       $sql = $sql .
           //"GROUP BY match_time, m.scheddname, m.cluster_id, m.proc_id " .
           "ORDER BY match_time desc, m.scheddname, m.cluster_id, " .
           "m.proc_id desc";

    unset($_SESSION["{$index}"]);    
    $_SESSION["{$index}"] = array();
    $_SESSION["{$index}"]["title"]="Historical information about matches for job {$schedd}.{$cid}.{$pid}.";
    $_SESSION["{$index}"]["message"]="Historical information about matches for job {$schedd}.{$cid}.{$pid}.";

    $fields=array();
    $fields[0]="match_time";
    $fields[1]="scheddname";
    $fields[2]="cluster";
    $fields[3]="proc";
    $fields[4]="startdname";

    $rs = new recordset($rcount, $fields, get_recordset($connection, $sql));
    return $rs;
  }
?>
